﻿/*  Copyright © 2012 Matthew David Elgert - mdelgert@yahoo.com
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation; either version 2.1 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA */

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;


namespace BLL
{

    public static class SMOUtil
    {

        public static void ScriptDBs()
        {
            using (DataTable dt = Common.GetDBNames())
            {
                foreach (DataRow row in dt.Rows)
                {
                    ScriptDB(row[0].ToString());
                }
            }
        }

        public static void ScriptTableData()
        {
            using (DataTable dt = Common.GetDBNames())
            {
                foreach (DataRow row in dt.Rows)
                {
                    ScriptTables(row[0].ToString());
                }
            }
        }

        private static void ScriptDB(string DB)
        {

            string DBName = String.Format(@"{0}{1}{2}\Schema", Common.GetTempDir(), DB, Common.GetTimeStamp());

            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
            {
                Server myServer = new Server(new ServerConnection(cnn));
                myServer.ConnectionContext.Connect();

                //Scripter scripter = new Scripter(myServer);

                Database MyDB = myServer.Databases[DB];
                ScriptingOptions scriptOptions = new ScriptingOptions();
                scriptOptions.ScriptDrops = true;
                scriptOptions.IncludeIfNotExists = true;

                CreateTempDir(DBName, "Tables");

                foreach (Table DBObject in MyDB.Tables)
                {
                    if (!DBObject.IsSystemObject) // Exclude system sobjects
                    {

                        StringCollection SPScripts = MyDB.Script(scriptOptions);
                        StreamWriter DBScript;
                        SPScripts = DBObject.Script();

                        foreach (string script in SPScripts)
                        {
                            DBScript = new StreamWriter(String.Format(@"{0}\Tables\{1}.sql", DBName, DBObject));
                            DBScript.Write(script);
                            DBScript.Close();
                        }
                        Log.Msg(String.Format("Script Schema Table {0} success", DBObject));
                    }
                }

                CreateTempDir(DBName, "StoredProcedures");

                foreach (StoredProcedure DBObject in MyDB.StoredProcedures)
                {
                    if (!DBObject.IsSystemObject) // Exclude system sobjects
                    {
                        StringCollection SPScripts = MyDB.Script(scriptOptions);
                        StreamWriter DBScript;
                        SPScripts = DBObject.Script();
                        foreach (string script in SPScripts)
                        {
                            DBScript = new StreamWriter(String.Format(@"{0}\StoredProcedures\{1}.sql", DBName, DBObject));
                            DBScript.Write(script);
                            DBScript.Close();
                        }
                        Log.Msg(String.Format("Script Schema Stored Procedure {0} success", DBObject));
                    }
                }

                CreateTempDir(DBName, "Views");

                foreach (View DBObject in MyDB.Views)
                {
                    if (!DBObject.IsSystemObject) // Exclude system sobjects
                    {
                        StringCollection SPScripts = MyDB.Script(scriptOptions);
                        StreamWriter DBScript;
                        SPScripts = DBObject.Script();
                        foreach (string script in SPScripts)
                        {
                            DBScript = new StreamWriter(String.Format(@"{0}\Views\{1}.sql", DBName, DBObject));
                            DBScript.Write(script);
                            DBScript.Close();
                        }
                        Log.Msg(String.Format("Script Schema View {0} success", DBObject));
                    }
                }

                CreateTempDir(DBName, "UserDefinedFunctions");

                foreach (UserDefinedFunction DBObject in MyDB.UserDefinedFunctions)
                {
                    if (!DBObject.IsSystemObject) // Exclude system sobjects
                    {
                        StringCollection SPScripts = MyDB.Script(scriptOptions);
                        StreamWriter DBScript;
                        SPScripts = DBObject.Script();
                        foreach (string script in SPScripts)
                        {
                            DBScript = new StreamWriter(String.Format(@"{0}\UserDefinedFunctions\{1}.sql", DBName, DBObject));
                            DBScript.Write(script);
                            DBScript.Close();
                        }
                        Log.Msg(String.Format("Script Schema User Defined Function {0} success", DBObject));
                    }
                }

                cnn.Close();

            }

        }

        private static void ScriptTables(string DB)
        {
            string DBName = String.Format(@"{0}{1}{2}\Data", Common.GetTempDir(), DB, Common.GetTimeStamp());

            CreateTempDir(DBName, "Tables");

            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
            {
                Server myServer = new Server(new ServerConnection(cnn));
                myServer.ConnectionContext.Connect();
                Database MyDB = myServer.Databases[DB];

                ScriptingOptions scriptOptions = new ScriptingOptions();
                scriptOptions.ScriptData = true;
                scriptOptions.ScriptDrops = false;
                scriptOptions.EnforceScriptingOptions = true;
                scriptOptions.ScriptSchema = false;
                scriptOptions.IncludeHeaders = true;
                scriptOptions.AppendToFile = true;
                scriptOptions.Indexes = true;
                scriptOptions.WithDependencies = true;

                foreach (Table DBObject in MyDB.Tables)
                {
                    if (!DBObject.IsSystemObject) // Exclude system sobjects
                    {
                        string file = DBName + @"\\Tables\\" + DBObject + @".sql";
                        scriptOptions.FileName = file;
                        DBObject.EnumScript(scriptOptions);
                        Log.Msg(String.Format("Script Data Table {0} success", DBObject));
                    }
                }

                cnn.Close();

            }

        }

        private static void CreateTempDir(string DBName, string DBObject)
        {
            if (!Directory.Exists(String.Format(@"{0}\{1}\", DBName, DBObject)))
            {
                try
                {
                    Directory.CreateDirectory(String.Format(@"{0}\{1}\", DBName, DBObject));
                }
                catch (System.Exception ex)
                {
                    Log.Msg(ex.ToString(), true);
                }
            }
        }

    }

}

